﻿using System;
using System.Data;


namespace TeachingAffairsManagementSystem
{
    public static class Operation
    {
        private static readonly DbHelper DbHelper = new DbHelper();

        #region 查询

        public static DataSet QueryStudentByS_Id(int s_id)
        {
            return DbHelper.ExecuteQuery($"select * from student where s_id = {s_id}");

        }

        public static DataSet QueryTeacherByT_Id(int t_id)
        {
            return DbHelper.ExecuteQuery($"select * from teacher where t_id = {t_id}");
        }


        #endregion


        #region 登录

        public static DataSet Login(string username, string password)
        {
            return DbHelper.ExecuteQuery($"call Login('{username}','{password}')");
        }

        #endregion


        #region 注册

        public static bool IsUsernameAvailable(string username)
        {
            return Convert.ToBoolean(DbHelper.ExecuteScalar($"SELECT IsUsernameAvailable('{username}')"));
        }


        public static bool Register(string username, string password, string email, string name, char gender)
        {
            return Convert.ToBoolean(DbHelper.ExecuteScalar($"call Register('{username}','{password}','{email}','{name}','{gender}')"));
        }

        #endregion


        #region 找回密码

        public static DataSet QueryStudentByUsername(string username)
        {
            return DbHelper.ExecuteQuery($"select * from student where username = '{username}'");
        }

        public static DataSet QueryTeacherByUsername(string username)
        {
            return DbHelper.ExecuteQuery($"select * from teacher where username = '{username}'");
        }

        #endregion
        

        #region 修改密码

        public static bool UpdateStudentPassword(int s_id, string password)
        {
            return Convert.ToBoolean(DbHelper.ExecuteScalar($"call UpdateStudentPassword({s_id},'{password}')"));
        }

        public static bool UpdateTeacherPassword(int t_id, string password)
        {
            return Convert.ToBoolean(DbHelper.ExecuteScalar($"call UpdateTeacherPassword({t_id},'{password}')"));
        }

        #endregion


        #region 学生操作

        public static bool IsLessonSelectable(int s_id,int l_id,int t_id)
        {
            return Convert.ToBoolean(DbHelper.ExecuteScalar($"select IsLessonSelectable({s_id},{l_id},{t_id})"));
        }

        public static bool SelectLesson(int s_id,int l_id,int t_id)
        {
            return Convert.ToBoolean(DbHelper.ExecuteScalar($"call SelectLesson({s_id},{l_id},{t_id})"));
        }

        public static bool DropLesson(int s_id, int l_id,int t_id)
        {
            return Convert.ToBoolean(DbHelper.ExecuteScalar($"call DropLesson({s_id},{l_id},{t_id})"));
        }

        public static bool UpdateStudent(int s_id, string name, char gender)
        {
            return Convert.ToBoolean(DbHelper.ExecuteScalar($"call UpdateStudent({s_id},'{name}','{gender}')"));
        }

        #endregion


        #region 教师操作


        public static bool UpdateTeacher(int t_id, string name, char gender)
        {
            return Convert.ToBoolean(DbHelper.ExecuteScalar($"call UpdateTeacher({t_id},'{name}','{gender}')"));
        }

        #endregion

        

    }
}